
package uit.jee.lib.access.mapper;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import uit.jee.lib.dto.BookDTO;

/**
 *
 * @author ngloc_it
 */
public class BookMapper extends MapperDB{
    private final String sqlQuery = "SELECT booklibrary.sach.MaSach, booklibrary.sach.MaLoaiSach, booklibrary.sach.TenSach, "
			 + "booklibrary.sach.NhaXuatBan, booklibrary.sach.NgayXuatBan, booklibrary.sach.NoiDungChinh, "
			 + "booklibrary.sach.MaTacGia, booklibrary.sach.LuotMuon, booklibrary.sach.TrangThai, "
			 + "booklibrary.sach.FileAnh, booklibrary.tacgia.TenTacGia, booklibrary.loaisach.TenLoaiSach "
                         + "FROM booklibrary.sach , booklibrary.tacgia, booklibrary.loaisach "
                         + "WHERE booklibrary.sach.MaTacGia = booklibrary.tacgia.MaTacGia And "
                         + "booklibrary.sach.MaLoaiSach = booklibrary.loaisach.MaLoaiSach";

    public BookMapper() throws Exception {
        super();
    }

    public int countBooks() throws Exception{
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT count(*) from booklibrary.sach");
        PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
        ResultSet rs = stmt.executeQuery();
        String count = "0";
        if(rs!=null && rs.next()){
            count = rs.getString(1);
        }
        return Integer.parseInt(count);
    }

    private void InitBookDTOFromRs(BookDTO book, ResultSet rs) throws Exception{
        if((rs != null) && (book != null)){
            book.setBookId(rs.getString(BookDTO.BOOK_ID));
            book.setBookTypeId(rs.getString(BookDTO.BOOK_BOOKTYPE_ID));
            book.setBookType(rs.getString(BookDTO.BOOK_BOOKTYPE));
            book.setAuthorId(rs.getString(BookDTO.BOOK_AUTHOR_ID));
            book.setName(rs.getString(BookDTO.BOOK_NAME));            
            book.setMainContent(rs.getString(BookDTO.BOOK_MAIN_CONTENT));
            book.setPublisher(rs.getString(BookDTO.BOOK_PUBLISHER));
            book.setPublisDate(rs.getString(BookDTO.BOOK_PUBLIST_DATE));
            book.setCounter(rs.getInt(BookDTO.BOOK_COUNTER));
            book.setStatus(rs.getInt(BookDTO.BOOK_STATUS));
            book.setImgPath(rs.getString(BookDTO.BOOK_IMG_PATH));
            book.setAuthor(rs.getString(BookDTO.BOOK_AUTHOR));
        }
    }

    public boolean CheckExitedBook(BookDTO book) throws Exception{
        boolean isExisted = false;

        try{
            StringBuffer sql = new StringBuffer();
            sql.append("SELECT * from booklibrary.sach Where ");
            sql.append(BookDTO.BOOK_ID).append(" = '");
            sql.append(book.getBookId()).append("'");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();
            if((rs != null) && (rs.next()))
                isExisted = true;
        }catch(Exception ex) {
            throw ex;
        }

        return isExisted;
    }

    public ArrayList<BookDTO> getAllBook() throws Exception{
        ArrayList<BookDTO> books = new ArrayList<BookDTO>();
        try {
            StringBuffer sql = new StringBuffer();
            sql.append(sqlQuery);
            PreparedStatement stmts = getConnection().prepareStatement(sql.toString());
            ResultSet re = stmts.executeQuery();

            while (re != null && re.next()) {
                BookDTO book = new BookDTO();
                InitBookDTOFromRs(book, re);
                books.add(book);
            }
        } catch (Exception ex) {
            throw ex;
        }

        return books;
    }

    /**
     * Get n books from start point (increa by default in database)
     * @param start start point
     * @param n num of book want check out
     * @return n book of smaller if database not enought
     * @throws Exception
     */
    public ArrayList<BookDTO> getTopN(int start, int n) throws Exception{
        ArrayList<BookDTO> books = new ArrayList<BookDTO>();
        StringBuffer sql = new StringBuffer();
        sql.append(sqlQuery).append(" order by booklibrary.sach.TenSach");

        PreparedStatement stmts = getConnection().prepareStatement(sql.toString());
        ResultSet re = stmts.executeQuery();

        int counterStartpoint = 0;
        int numBook = n;
        while (re != null && re.next()) {
            if (counterStartpoint++ >= start) {
                BookDTO book = new BookDTO();
                InitBookDTOFromRs(book, re);
                books.add(book);

                numBook--;
                if(numBook <= 0)
                    break;
            }
        }
        return books;
    }

    public ArrayList<BookDTO> getTopTen() throws Exception{
        ArrayList<BookDTO> books = new ArrayList<BookDTO>();
        StringBuffer sql = new StringBuffer();
        sql.append(sqlQuery).append(" order by booklibrary.sach.LuotMuon DESC Limit 4");

        PreparedStatement stmts = getConnection().prepareStatement(sql.toString());
        ResultSet re = stmts.executeQuery();

        while (re != null && re.next()) {
            BookDTO book = new BookDTO();
            InitBookDTOFromRs(book, re);
            books.add(book);
        }
        return books;
    }

    public BookDTO getRandomBook() throws Exception{
        BookDTO book = new BookDTO();
        StringBuffer sql = new StringBuffer();
        sql.append(sqlQuery);
        sql.append(" order by rand() limit 1");
        PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
        ResultSet rs = stmt.executeQuery();
        if((rs != null) &&
                (rs.next())){
            InitBookDTOFromRs(book, rs);
        }
        return book;
    }

    public BookDTO getBookById(String bookId) throws Exception{
        BookDTO books = new BookDTO();
        try {
            StringBuffer sql = new StringBuffer();
            sql.append(sqlQuery).append(" And ");
            sql.append(BookDTO.BOOK_ID).append("= '").append(bookId).append("'");
            PreparedStatement stmts = getConnection().prepareStatement(sql.toString());
            ResultSet re = stmts.executeQuery();

            if (re != null && re.next()) {
                InitBookDTOFromRs(books, re);
            }
        } catch (Exception ex) {
            throw ex;
        }

        return books;
    }

    public ArrayList<BookDTO> getBookByName(String bookName) throws Exception{
        ArrayList<BookDTO> books = new ArrayList<BookDTO>();
        try {
            StringBuffer sql = new StringBuffer();
            sql.append(sqlQuery).append(" And ");
            sql.append(BookDTO.BOOK_NAME).append("='").append(bookName).append("'");
            PreparedStatement stmts = getConnection().prepareStatement(sql.toString());
            ResultSet re = stmts.executeQuery();

            while (re != null && re.next()) {
                BookDTO book = new BookDTO();
                InitBookDTOFromRs(book, re);
                books.add(book);
            }
        } catch (Exception ex) {
            throw ex;
        }

        return books;
    }

    public ArrayList<BookDTO> getBookByAuthorName(String authorName) throws Exception{
        return null;
    }

    public ArrayList<BookDTO> getBookByAuthorId(String authorId) throws Exception{
        ArrayList<BookDTO> books = new ArrayList<BookDTO>();
        try {
            StringBuffer sql = new StringBuffer();
            sql.append(sqlQuery).append(" And ");
            sql.append(BookDTO.BOOK_AUTHOR_ID).append("= '").append(authorId).append("'");
            PreparedStatement stmts = getConnection().prepareStatement(sql.toString());
            ResultSet re = stmts.executeQuery();

            while (re != null && re.next()) {
                BookDTO book = new BookDTO();
                InitBookDTOFromRs(book, re);
                books.add(book);
            }
        } catch (Exception ex) {
            throw ex;
        }

        return books;
    }

    public ArrayList<BookDTO> getBookByBookTypeName(String bookTypeName) throws Exception{
        return null;
    }

    public ArrayList<BookDTO> getBookByBookTypeId(String bookTypeId) throws Exception{
        ArrayList<BookDTO> books = new ArrayList<BookDTO>();
        try {
            StringBuffer sql = new StringBuffer();
            sql.append(sqlQuery).append(" And booklibrary.sach.");
            sql.append(BookDTO.BOOK_BOOKTYPE_ID).append("= '").append(bookTypeId).append("'");
            PreparedStatement stmts = getConnection().prepareStatement(sql.toString());
            ResultSet re = stmts.executeQuery();

            while (re != null && re.next()) {
                BookDTO book = new BookDTO();
                InitBookDTOFromRs(book, re);
                books.add(book);
            }
        } catch (Exception ex) {
            throw ex;
        }

        return books;
    }

    public void Insert(BookDTO book) throws Exception {
        try {
            StringBuffer sql = new StringBuffer();
            sql.append("Insert into booklibrary.sach values( ");
            sql.append("'").append(book.getBookId()).append("', ");
            sql.append("'").append(book.getBookTypeId()).append("', ");
            sql.append("'").append(book.getName()).append("', ");            
            sql.append("'").append(book.getPublisher()).append("', ");
            sql.append("'").append(book.getPublisDate()).append("', ");
            sql.append("'").append(book.getMainContent()).append("', ");
            sql.append("").append(book.getCounter()).append(", ");
            sql.append("'").append(book.getImgPath()).append("', ");
            sql.append("'").append(book.getStatus()).append("', ");
            sql.append("'").append(book.getAuthorId()).append("') ");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.execute();
            stmt.close();
        } catch (Exception e) {
            throw e;
        }
    }

    public void Insert(BookDTO[] book) throws Exception {
    }

    public void DeleteById(String bookId) throws Exception {
        try {
            StringBuffer sql = new StringBuffer();
            sql.append("delete * from booklibrary.sach where ");
            sql.append(BookDTO.BOOK_ID).append(" ='").append(bookId).append("'");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.execute();
            stmt.close();
        } catch (Exception ex) {
            throw ex;
        }
    }

    public void Delete(BookDTO book) throws Exception {
        try {
            StringBuffer sql = new StringBuffer();
            sql.append("delete * from booklibrary.sach where ");
            sql.append(BookDTO.BOOK_ID).append(" ='").append(book.getBookId()).append("'");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.execute();
            stmt.close();
        } catch (Exception ex) {
            throw ex;
        }
    }

    public void Delete(BookDTO[] books) throws Exception {
    }

    public void Update(BookDTO book) throws Exception {
        try {
            StringBuffer sql = new StringBuffer();
            sql.append("Update booklibrary.sach Set ");
            sql.append(BookDTO.BOOK_ID).append("='").append(book.getBookId()).append("', ");
            sql.append(BookDTO.BOOK_BOOKTYPE_ID).append("='").append(book.getBookTypeId()).append("', ");
            sql.append(BookDTO.BOOK_NAME).append("='").append(book.getName()).append("', ");            
            sql.append(BookDTO.BOOK_PUBLISHER).append("='").append(book.getPublisher()).append("', ");
            sql.append(BookDTO.BOOK_PUBLIST_DATE).append("='").append(book.getPublisDate()).append("', ");
            sql.append(BookDTO.BOOK_MAIN_CONTENT).append("='").append(book.getMainContent()).append("', ");
            sql.append(BookDTO.BOOK_AUTHOR_ID).append("='").append(book.getAuthorId()).append("' ");
            sql.append(BookDTO.BOOK_COUNTER).append("='").append(book.getCounter()).append("' ");
            sql.append(BookDTO.BOOK_IMG_PATH).append("='").append(book.getImgPath()).append("' ");
            sql.append(BookDTO.BOOK_STATUS).append("='").append(book.getStatus()).append("' ");


            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.executeUpdate();
            stmt.close();
        } catch (Exception ex) {
            throw ex;
        }
    }

    public void Update(BookDTO[] books) throws Exception {
    }
}

